Oracle SQL Explain Plan	May 7, 2007 11:28:18 AM PDT

Target:

	Version: Oracle 9.2.0.4.0
	Database: ILOLI
	Schema: PROD_HSPOA2
	Date: 07-May-07 4:00:00 PM

SQL Statement:

SELECT DISTINCT organization.org_key, organization.org_name FROM organization, assigned_exam_rpt_data WHERE organization.parent_org_key = 1076 AND assigned_exam_rpt_data.organization_key = organization.org_key AND assigned_exam_rpt_data.subject = 1 AND assigned_exam_rpt_data.graded_date IS NOT NULL AND assigned_exam_rpt_data.class_active_status = 1 AND assigned_exam_rpt_data.student_active_status = 1

Optimizer Mode Used:

COST ALL ROWS (optimizer: CHOOSE)

Total Cost:

9,369

Execution Steps:

Step #	Step Name

27

SELECT STATEMENT

26

SORT [UNIQUE]

25

HASH JOIN

1

PROD_HSPOA2.CLASS_PD TABLE ACCESS [FULL]

24

NESTED LOOPS [OUTER]

22

HASH JOIN

20

HASH JOIN [OUTER]

18

HASH JOIN [OUTER]

16

PROD_HSPOA2.ORGANIZATION TABLE ACCESS [BY INDEX ROWID]

15

NESTED LOOPS

8

NESTED LOOPS

6

HASH JOIN

2

PROD_HSPOA2.ASSN_EXAM_STAN TABLE ACCESS [FULL]

5

HASH JOIN

3

PROD_HSPOA2.EXAM TABLE ACCESS [FULL]

4

PROD_HSPOA2.ASSN_EXAM_DATA TABLE ACCESS [FULL]

7

PROD_HSPOA2.SYS_C0011090 INDEX [UNIQUE SCAN]

14

BITMAP CONVERSION [TO ROWIDS]

13

BITMAP AND

10

BITMAP CONVERSION [FROM ROWIDS]

9

PROD_HSPOA2.ORGANIZATION_PARENT_ORG INDEX [RANGE SCAN]

12

BITMAP CONVERSION [FROM ROWIDS]

11

PROD_HSPOA2.SYS_C0011047 INDEX [RANGE SCAN]

17

PROD_HSPOA2.ASSIGNMENT TABLE ACCESS [FULL]

19

PROD_HSPOA2.BASE_USER TABLE ACCESS [FULL]

21

PROD_HSPOA2.BASE_USER TABLE ACCESS [FULL]

23

PROD_HSPOA2.SYS_C0011047 INDEX [UNIQUE SCAN]

Step #	Description	Est. Cost	Est. Rows Returned	Est. KBytes Returned

1

This plan step retrieves all rows from table CLASS_PD.

9

14,779

115.461

2

This plan step retrieves all rows from table ASSN_EXAM_STAN.

147

503,569

11,802.398

3

This plan step retrieves all rows from table EXAM.

7

2,240

17.5

4

This plan step retrieves all rows from table ASSN_EXAM_DATA.

489

771,761

15,073.457

5

This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.

515

503,071

13,755.848

6

This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.

1,244

503,071

25,546.574

7

This plan step retrieves a single ROWID from the B*-tree index SYS_C0011090.

--

1

0.005

8

This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.

1,244

499,731

27,817.058

9

This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index ORGANIZATION_PARENT_ORG.

--

963

--

10

This plan step accepts a ROWID from its child node, and converts it to a bitmap representation of the index.

11

This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index SYS_C0011047.

--

963

--

12

This plan step accepts a ROWID from its child node, and converts it to a bitmap representation of the index.

13

This plan step has no supplementary description information.

--

--

--

14

This plan step accepts a bitmap representation of an index from its child node, and converts it to a ROWID that can be used to access the table.

15

This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.

9,102

3,447

309.691

16

This plan step retrieves rows from table ORGANIZATION through ROWID(s) returned by an index.

9,102

1

0.034

17

This plan step retrieves all rows from table ASSIGNMENT.

9

23,915

163.481

18

This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.

9,118

3,447

333.255

19

This plan step retrieves all rows from table BASE_USER.

76

161,000

1,415.039

20

This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.

9,217

3,447

363.551

21

This plan step retrieves all rows from table BASE_USER.

76

80,500

628.906

22

This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.

9,307

3,447

390.48

23

This plan step retrieves a single ROWID from the B*-tree index SYS_C0011047.

--

1

0.004

24

This plan step joins two sets of rows by iterating over the outer, or driving, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause; in addition to the rows satisfying that condition, Oracle also returns all rows from the row set not containing the outer join operator (+) which failed to satisfy that condition.

9,307

3,447

403.945

25

This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.

9,321

3,446

430.75

26

This plan step accepts a row set (its only child) and sorts it in order to identify and eliminate duplicates.

9,369

18

2.25

27

This plan step designates this statement as a SELECT statement.

9,369

18

2.25