The following query takes 11 hours to run.
SELECT DISTINCT A. NODO_ID, B. NODO_B_ID NODO_EQ1 BITOWN03.BS_R_NODOS_BIT_03 FROM A BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03 B, C
BITOWN03.TMP_NODOS_OK_EST_BIT_03 NODO_ID = B WHERE A. AND B. NODO_A_ID NODO_B_ID = C. NODO_ID;
the following implementation plan SQL> @ c: \\ oracle \\ ora92 \\ rdbms \\ admin \\ utlxpls PLAN_TABLE_OUTPUT --------------------------------------- ----------------------------------------------
- -------------------------------------------------- 
-------------------------------
------------------------------------------------- ---------------------------------- Predicate Information (identified by Operation id): ---- ----------------------------------------------- ------------------------------- 7 - access ("B". "NODO_B_ID" = "C". "NODO_ID" AND "A". "NODO_ID" = "B". "NODO_A_ID)
Note: CPU Costing is off
** ************************************************** ****************************
SOLUTION to the case.
************************************************ ******************************** Another
Cartesian product. In this case the Cartesian knows what he does. Cross almost 6,000 million rows (a total of 61 gigabytes of data) and at minimal cost. What a paradox.
A detail to understand this decision: there are no restrictions on Primary Key or Foreign Key
or unique indexes, constraints or Not Null.
Because of this, Oracle is practical to combine all the results of a table (4.5 million) over the 1400 rows in another table, a
"everyone with everyone"
.
Not bad. However, no information to Oracle, much to analyze the tables, it will not be able to get
priori. We are interested only unique values \u200b\u200bin the relationship table, which exist in their related tables, but as I say, there is nothing to provide that information to Oracle.
change some details of the consultation and leave it like that:
SELECT DISTINCT A.
NODO_ID, B. NODO_B_ID NODO_EQ1
FROM BITOWN03.BS_R_NODOS_BIT_03 A, (select distinct nodo_a_id, nodo_b_id from BITOWN03 . RE_R_CONEX_EXTERNAS_BIT_03) B, C
BITOWN03.TMP_NODOS_OK_EST_BIT_03
NODO_ID = B WHERE A. AND B. NODO_A_ID
NODO_B_ID = C. NODO_ID;
Report of two things: that I have interest in obtaining the separate codes in the relationship table, and also exist in the other two tables.
The implementation plan to run completely changed as I have said otherwise
. Now Oracle does this other implementation plan.
SQL> @ c: \\ oracle \\ ora92 \\ rdbms \\ admin \\ utlxpls
PLAN_TABLE_OUTPUT -------------------------- --------------------------------------------------
----------------- ------------------------------- --------------------------------------------------
------------ 81M
13626 cost units. Regarding the previous cost of only 23. But now appears the Cartesian and it seems that the implementation is more faithful to what we want. I have just released
implementation: one minute and twenty seconds. Well
0 comments:
Post a Comment