Oracle SQL Explain Plan	May 7, 2007 11:29:33 AM PDT

Target:

	Version: Oracle 9.2.0.1.0
	Database: LOLI
	Schema: HSP
	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:

4,221

Execution Steps:

Step #	Step Name

22

SELECT STATEMENT

21

SORT [UNIQUE]

20

HASH JOIN

1

HSP.CLASS_PD TABLE ACCESS [FULL]

19

HASH JOIN

2

HSP.EXAM TABLE ACCESS [FULL]

18

HASH JOIN

16

NESTED LOOPS [OUTER]

14

NESTED LOOPS

12

HASH JOIN

4

HSP.ORGANIZATION TABLE ACCESS [BY INDEX ROWID]

3

HSP.ORGANIZATION_PARENT_ORG INDEX [RANGE SCAN]

11

HASH JOIN

5

HSP.ASSN_EXAM_DATA TABLE ACCESS [FULL]

10

HASH JOIN [OUTER]

8

HASH JOIN [OUTER]

6

HSP.ASSN_EXAM_STAN TABLE ACCESS [FULL]

7

HSP.ASSIGNMENT TABLE ACCESS [FULL]

9

HSP.BASE_USER TABLE ACCESS [FULL]

13

HSP.SYS_C006878 INDEX [UNIQUE SCAN]

15

HSP.SYS_C006728 INDEX [UNIQUE SCAN]

17

HSP.BASE_USER TABLE ACCESS [FULL]

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

1

This plan step retrieves all rows from table CLASS_PD.

19

14,534

113.547

2

This plan step retrieves all rows from table EXAM.

19

2,213

12.967

3

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

1

950

--

4

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

2

5

0.156

5

This plan step retrieves all rows from table ASSN_EXAM_DATA.

1,095

766,185

12,719.868

6

This plan step retrieves all rows from table ASSN_EXAM_STAN.

346

486,561

11,403.773

7

This plan step retrieves all rows from table ASSIGNMENT.

19

22,885

156.44

8

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.

806

486,561

14,729.874

9

This plan step retrieves all rows from table BASE_USER.

189

159,656

1,403.227

10

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,592

486,561

19,006.289

11

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.

3,885

486,561

27,083.962

12

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.

3,901

3,418

297.072

13

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

--

1

0.004

14

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.

3,901

3,397

308.517

15

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

--

1

0.003

16

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.

3,901

3,397

318.469

17

This plan step retrieves all rows from table BASE_USER.

189

79,828

623.656

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.

4,125

3,397

345.008

19

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.

4,146

3,397

364.912

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.

4,176

3,396

391.336

21

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

4,221

18

2.074

22

This plan step designates this statement as a SELECT statement.

4,221

18

2.074