Thanks Sean.
I did it, and selectivity is better:
Query
------------------------------------------------
select distinct
articles.art_id,
articles.art_article_nr,
generic_articles.ga_id,
link_art.la_id,
link_la_typ.lat_sort sort,
link_la_typ.lat_sup_id bra_id,
articles.art_replacement
from link_la_typ
inner join generic_articles on lat_ga_id = ga_id
inner join link_art on lat_la_id = la_id
inner join articles on la_art_id = art_id
where lat_typ_id=:TYP_ID and ga_id=:GA_ID
ORDER BY 7 ASC
, 2 ASC
Plan
------------------------------------------------
PLAN SORT (SORT (JOIN (GENERIC_ARTICLES INDEX (GENERIC_ARTICLES_IDX1),
LINK_LA_TYP INDEX (LINK_LA_TYP_IDX5), LINK_ART INDEX (LINK_ART_IDX1), ARTICLES
INDEX (ARTICLES_IDX1))))
Query Time
------------------------------------------------
Prepare : 31.00 ms
Execute : 3,900.00 ms
Avg fetch time: 185.71 ms
Memory
------------------------------------------------
Current: 10,324,808
Max : 129,710,544
Buffers: 2,048
Operations
------------------------------------------------
Read : 3,049
Writes : 0
Fetches: 3,794
Marks : 0
As you can see, execute time is again about 4 seconds - any ideas why when now
3 tables return 79 records, and one table return just one row ?
And, quantity of rows in tables are:
LINK_LA_TYP = 125.000.000
GENERIC_ARTICLES = 5.400 rows
ARTICLES = 4.400.000
LINK_ART = 18.300.000
I know that tables are huge, but again, query reads only 250 records.
Regards,
Zoran
