No question at this time
DBA Top 10
1 M. Cadot 7200
2 B. Vroman 3100
3 J. PĂ©ran 2100
4 A. Kavsek 1400
5 P. Tsongayinwe 1100
6 P. Wisse 600
7 D. Walgude 400
7 J. Schnackenberg 400
9 J. Alcroft 200
10 A. Hudspith 100
10 D. Johnson 100
10 B. B 100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48684
Total active users1328
Act. users last 24h1
Act. users last hour0
Registered user hits last week67
Registered user hits last month224
Go up

strange index-name ( index$_join$_9 and index$_join$_8 )
Next thread: Not able to use the db link through PL/SQL
Prev thread: TEMPORARY TABLESPACE sizing

Message Score Author Date
hello, who can tell me what are index$_join$_9 ...... ich name Apr 08, 2010, 11:23
Hi, Those are not real objects, they are views us...... Franck Pachot Apr 08, 2010, 11:37
Hi Franck, is it possible to find the definitio...... ich name Apr 08, 2010, 12:37
Hi, I suppose that if you create an index that ...... Franck Pachot Apr 08, 2010, 13:30
Here is an example: <i>I create a table with tw...... Score: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 Pts Franck Pachot Apr 08, 2010, 13:51
Hi Franck, Thank you for your help. I create...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts ich name Apr 08, 2010, 14:36
Hi, The index join has disappeared from the exp...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Apr 09, 2010, 22:26

Follow up by mail Click here


Subject: strange index-name ( index$_join$_9 and index$_join$_8 )
Author: ich name, Germany
Date: Apr 08, 2010, 11:23, 4511 days ago
Os info: Ms Windows
Oracle info: 10
Message: hello,

who can tell me what are index$_join$_9 and index$_join$_8 (see column Obj. Name) ?

Both indexes are not in the view all_indexes.
can I drop them?
I'm looking for a way to see the definition of both indexes to create my own Indexes and replace them.

Kind regards
Belinda
-------------------


Description Objectname Cost Cardinality Bytes IO Cost Time Pos Options Optimizer Opertaion Obj Type Obj Instan. CPU Cost

SELECT STATEMENT, GOAL = CHOOSE 692 1 950 682 9 692 CHOOSE SELECT STATEMENT 135373534
HASH JOIN 692 1 950 682 9 1 HASH JOIN 135373534
HASH JOIN 366 1 231 360 5 1 HASH JOIN 87387348
NESTED LOOPS 184 1 161 181 3 1 NESTED LOOPS 39697188
TABLE ACCESS BY INDEX ROWID T_TABLE_1 2 1 58 2 1 1 BY INDEX ROWID ANALYZED TABLE ACCESS TABLE 5 15763
INDEX UNIQUE SCAN IP1_T_TABLE_1 1 1 1 1 1 UNIQUE SCAN ANALYZED INDEX INDEX (UNIQUE) 8171
VIEW V_VIEW_S 182 1 103 179 3 2 VIEW VIEW 6 39681425
WINDOW SORT 182 9352 205744 179 3 1 SORT WINDOW 39681425
VIEW -->index$_join$_9 115 9352 205744 114 2 1 VIEW VIEW 9 18966932
HASH JOIN 1 HASH JOIN
INDEX RANGE SCAN IP1_T_TABLE_2 67 9352 205744 65 1 1 RANGE SCAN ANALYZED INDEX INDEX (UNIQUE) 23624244
INDEX FAST FULL SCAN IU1_T_TABLE_2 90 9352 205744 90 2 2 FAST FULL SCAN ANALYZED INDEX INDEX (UNIQUE) 5316680
VIEW V_VIEW_S 182 9352 654640 179 3 2 VIEW VIEW 7 39681425
WINDOW SORT 182 9352 205744 179 3 1 SORT WINDOW 39681425
VIEW --->index$_join$_8 115 9352 205744 114 2 1 VIEW VIEW 8 18966932
HASH JOIN 1 HASH JOIN
INDEX RANGE SCAN IP1_T_TABLE_2 67 9352 205744 65 1 1 RANGE SCAN ANALYZED INDEX INDEX (UNIQUE) 23624244
INDEX FAST FULL SCAN IU1_T_TABLE_2 90 9352 205744 90 2 2 FAST FULL SCAN ANALYZED INDEX INDEX (UNIQUE) 5316680
VIEW V_VIEW_S 325 9352 6724088 322 4 2 VIEW VIEW 3 39977451
WINDOW SORT 325 9352 785568 322 4 1 SORT WINDOW 39977451
TABLE ACCESS FULL T_TABLE_2 139 9352 785568 138 2 1 FULL ANALYZED TABLE ACCESS TABLE 4 17366756
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: strange index-name ( index$_join$_9 and index$_join$_8 )
Author: Franck Pachot, Switzerland
Date: Apr 08, 2010, 11:37, 4511 days ago
Message: Hi,
Those are not real objects, they are views used by the optimizer when it can do the join with index data only (without going to the table)
You see under index$_join$_9 an hash join between two indexes.
Regards,
Franck.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: strange index-name ( index$_join$_9 and index$_join$_8 )
Author: ich name, Germany
Date: Apr 08, 2010, 12:37, 4511 days ago
Message: Hi Franck,

is it possible to find the definition of this Indexes?

I'm intrested to create to Indexes to be used instead of both indexes.


Thanks
Belinda
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: index join
Author: Franck Pachot, Switzerland
Date: Apr 08, 2010, 13:30, 4511 days ago
Message: Hi,

I suppose that if you create an index that concatenates columns from index IP1_T_TABLE_2 and columns from index IU1_T_TABLE_2 then that index can be used instead of that index join operation.

Regards,
Franck.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: index join
Author: Franck Pachot, Switzerland
Date: Apr 08, 2010, 13:51, 4511 days ago
Score:   Score: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 Pts
Message: Here is an example:

I create a table with two columns: n1 and n2

SQL> create table t as select mod(rownum,10) n1 ,mod(rownum,8) n2 , lpad('x',1000,'x') x from dual connect by level < 1000;
Table created.

and I create an index on each column

SQL> create index ti1 on t(n1);
Index created.

SQL> create index ti2 on t(n2);
Index created.

I set autotrace to see the execution plan

SQL> set autotrace trace explain

and run a query with predicates on n1 and n2

SQL> select n1,n2 from t where n2 is not null and n1=3;

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97 | 2522 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 97 | 2522 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | TI1 | 97 | 2522 | 1 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| TI2 | 97 | 2522 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------->
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N2" IS NOT NULL AND "N1"=3)
2 - access(ROWID=ROWID)
3 - access("N1"=3)
4 - filter("N2" IS NOT NULL)


You see the join index:
- range scan on TI1 to get rowid's that verifies predicate n1=3
- result is hashed for hash join
- full scan of TI2 to get rowid's that verifies predicate n2 is not null
- that is hash joined with the first one. You see the jon condition ROWID=ROWID
- the result from that operation, called index$_join$_001, has rowid,n2,and n2, exactly as if it were an index on (n1,n2) that has been created 'on the fly'


now we create a similar index

SQL> create index ti12 on t(n1,n2);
Index created.

and run the same statement

SQL> select n1,n2 from t where n2 is not null and n1=3;

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97 | 2522 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TI12 | 97 | 2522 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N1"=3)
filter("N2" IS NOT NULL)


Then you see that the index we have created is used, and cost is a bit smaller
Up to you to see if the improvement in the query worth the cost of an additional index.

Thanks for the question, I've seen that in the past but did not take time to understand and reproduce it ;)

Regards,
Franck.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: index join
Author: ich name, Germany
Date: Apr 08, 2010, 14:36, 4511 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Franck,

Thank you for your help.

I created an Index (INB1_T_2) as you described and there is the new out put.




-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Description |Objectname |Cost |Cardinality |Bytes |IO Cost|Time |Pos |Options Optim. |Opertaion |Obj Type |Obj Instan. |CPU Cost
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT STATEMENT, GOAL = CHOOSE | | |501 |1 |950 |494 |7 |501 | CHOOSE |SELECT STTEMNT | | |97070650
HASH JOIN | |501 |1 |950 |494 |7 |1 | | |HASH JOIN | | |97070650
NESTED LOOPS | |414 |468 |396396 |409 |5 |1 | | |NESTED LOOPS | | |64492539
NESTED LOOPS | |89 |1 |128 |87 |2 |1 | | |NESTED LOOPS | | |24515089
TABLE ACCESS BY INDEX ROWID |T_TABLE_1 |2 |1 |58 |2 |1 |1 |BY INDEX ROWID |ANALYZED |TABLE ACCESS |TABLE | 5 |15763
INDEX UNIQUE SCAN |IP1_T_TABLE_1 |1 |1 | |1 |1 |1 |UNIQUE SCAN |ANALYZED |INDEX |INDEX (UNIQUE) | |8171
VIEW |V_VIEW_S |87 |1 |70 |85 |2 |2 | | |VIEW |VIEW | 7 |24499326
WINDOW SORT | |87 |9352 |205744 |85 |2 |1 |SORT | |WINDOW | | |24499326
INDEX FAST FULL SCAN |-->INB1_T_2 |20 |9352 |205744 |20 |1 |1 |FAST FULL SCAN |ANALYZED |INDEX |INDEX | |3784832
VIEW |V_VIEW_S |325 |468 |336492 |322 |4 |2 | | |VIEW |VIEW | 3 |39977451
WINDOW SORT | |325 |9352 |785568 |322 |4 |1 |SORT | |WINDOW | | |39977451
TABLE ACCESS FULL |T_TABLE_2 |139 |9352 |785568 |138 |2 |1 |FULL |ANALYZED |TABLE ACCESS |TABLE | 4 |17366756
VIEW V_VIEW_S | |87 |9352 |963256 |85 |2 |2 | | |VIEW |VIEW | 6 |24499326
WINDOW SORT | |87 |9352 |205744 |85 |2 |1 |SORT | |WINDOW | | |24499326
INDEX FAST FULL SCAN |-->INB1_T_2 |20 |9352 |205744 |20 |1 |1 |FAST FULL SCAN |ANALYZED |INDEX |INDEX | |3784832
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: index join
Author: Philip Wisse, Netherlands
Date: Apr 09, 2010, 22:26, 4510 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

The index join has disappeared from the explain plan, but I guess the query still doesn't perform. I'll try to explain why. And what you can (try to) do about it.

For low cardinality nested loops are the best, for high cardinality hash joins are inevitable.
These 2 join methods are utterly different (For instance, nested loops work better when indexes are present. Hash joins combine well with full table scans, completely ignoring indexes).
Therefore explain plans which combine these 2 join methods are suspicious right from the start.

Oracle simply has not got the faintest idea how many rows wil reside after the first join.
If tables would be filled at random then few records will survive the first join.
But in a relational model (like master-detail) ALL the records will survive the join by design.

So how is Oracle going to estimate the cardinality of a view? Well, the optimizer uses table statistics, index statistics and column statistics (histograms).
You can provide table statistics by analyzing the table.
You can provide index statistics by analyzing the table with 'Cascade => TRUE' option.
You can provide column statistics by analyzing the table with 'FOR ALL [] COLUMNS' options. There are several flavours:
FOR ALL INDEXED COLUMNS
FOR ALL COLUMNS
You can also specify the number of buckets:
FOR ALL COLUMNS SIZE 20 -- small
FOR ALL COLUMNS SIZE 254 -- large
FOR ALL COLUMNS SIZE AUTO -- smart default
The AUTO option detects skewed (unevenly distributed) data.
In order to provide the maximum of statistics use a command like:
BEGIN

DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'SCOTT'
,TabName => 'T_TABLE_1'
,Estimate_Percent => 100
,Method_Opt => 'FOR ALL COLUMNS SIZE 254'
,Degree => 4
,Cascade => TRUE
);
END;



Of course detailed analyzing takes more time, there may be a limit.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here