PK Flashcards
PK
Select *
Merge Join, Sort Join/Buffer Sort
Select Distinct
n/a
From t1, t2
Table Access By Index ROWID, Index Full Scan
Use_Hash or No_Index
Force Hash Join
Index
n/a
Use_NL
Nested Loops
Group by Order By
Sort Group By
Order By
n/a
Group By
Hash Group By
Distinct and order by
n/a
Hash_Aggregation and order by
Force Sort Order By
Group By Cube
Sort Group By, Generate Cube, Sort Group By
Group By Rollup
Sort Group By Rollup
Cumulative Aggregate
Windows NoSort, Sort Group By
Moving Aggregate
Windows Buffer, Sort Group By
Rank() Over of Rank() Over Partition
Window Sort, Hash Group By
Multiple Rank() Over Partitions
Window Sort, Window Sort, Hash Group By
Use_Merge
n/a
Add Hint syntax
/*+ USE_xxx (t1 t2) */
hint for nested loop
/* USE_NL (t1 ,t2) */
Ignore pk hint
/*+ NO_INDEX ( x xxx_id_pk) */
Create an index on a table
CREATE INDEX xxx_id_pk on xxx (xxxID)
Add an ordered hint
/*+ ordered */
Add a pk on existing table
Alter table xxxx add constraint xxx_id_pk primary key (xxxID)
