Extended Statistics
Real data often shows correlations between column values. Because the optimizer does not know these real-world relationships, it will have difficulty estimating the cardinality and may pick sub-optimal execution plans for your queries. For example, it may join tables in an inefficient order or ignore the most efficient index etc.
Let’s create a very simple table to demonstrate how optimizer miscalculates the cardinality when there are correlated columns in WHERE clause:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE sampletable( id NUMBER, hotelname VARCHAR2(50), city VARCHAR2(50), country VARCHAR2(20)); INSERT INTO sampletable VALUES( 1, 'Pullman','Barcelona','Spain' ); INSERT INTO sampletable VALUES( 2, 'Gran Melia Fenix','Madrid','Spain' ); INSERT INTO sampletable VALUES( 3, 'Melia Castilla','Madrid','Spain' ); INSERT INTO sampletable VALUES( 4, 'Trump International','New York','US' ); INSERT INTO sampletable VALUES( 5, 'Four Seasons','New York','US' ); INSERT INTO sampletable VALUES( 6, 'Ambasciatori Palace','Rome','Italy' ); COMMIT; |