Home » Server Options » Replication » Using materialized view for Max(Score(0) ) Intermedia
| Using materialized view for Max(Score(0) ) Intermedia [message #75363] |
Fri, 02 May 2003 04:19 |
Jacob Thomas
Messages: 1
Registered: May 2003
|
Junior Member |
|
|
OS Solaris 2.6
DB Oracle 9.2.0
Hi
I have materialized been created on the following two tables but while excuting
the query the Materialized view is taken in some case but not in
some case . I need some wayout by which I could try to use the mv's in these case also
As per the setting mv's it is all fine
Set as query rewrite enabled and query integrity as stale / trusted
The problem is as follows
Table :Test_Doc
REPORT_NUM NOT NULL NUMBER(10)
PAGE_NUM NOT NULL NUMBER(4)
BILLING_STATUS VARCHAR2(1)
ASCII_PAGE_NUM NUMBER(4)
PUBLICATION_DATE DATE
PAGE_TEXT BINARY FILE LOB
The foreign key relation to test_doc_info table(Parent table)- (Report_num)
Have the following index on the table
1. REPORT_NUM, PAGE_NUM - Normal
2. REPORT_NUM, PUBLICATION_DATE- Normal Index
3.PAGE_TEXT - Intermedia Local Index
Table : Test_doc_info
REPORT_NUM NUMBER(10)
SOURCE_CODE VARCHAR2(6)
REPORT_TITLE VARCHAR2(70)
AUTHOR_ID NUMBER
PUBLICATION_DATE DATE
LOAD_DATE DATE
EMBARGO_DATE DATE
UPDATE_DATE DATE
REPORT_TYPE CHAR(2)
COLLECTION VARCHAR2(3)
SUB_COLLECTION VARCHAR2(6)
LANGUAGE VARCHAR2(20)
CD_NUM VARCHAR2(6)
PREVIEW VARCHAR2(1000)
SOURCE_NAME VARCHAR2(70)
Have the following index
1.Report_num - PK
2. Load_date
3.Author_id
4.Publication_date
5.Source_code
6.Report_title
Now I have the following materialized index with the following
script
CREATE MATERIALIZED VIEW TREDBA.MV_DOC_INFO
NOLOGGING CACHE
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT
/*+ ORDERED */ count(*) count,
tester_doc_info.publication_date,
tester_doc_info.report_title,
tester_doc_info.source_name,
tester_doc_info.source_code,
tester_doc_info.report_num,
tester_doc_info.cd_num,
tester_doc_info.collection,
tester_doc_info.sub_collection,
tester_doc_info.language,
tester_doc_info.embargo_date,
tester_doc_info.report_type,
tester_doc_info.author_id
FROM
test_doc,
tester_doc_info
WHERE
test_doc.report_num = tester_doc_info.report_num
GROUP BY tester_doc_info.publication_date,
tester_doc_info.report_title,
tester_doc_info.source_name,
tester_doc_info.source_code,
tester_doc_info.report_num,
tester_doc_info.cd_num,
tester_doc_info.collection,
tester_doc_info.sub_collection,
tester_doc_info.language,
tester_doc_info.embargo_date,
tester_doc_info.report_type,
tester_doc_info.author_id
Once I create the index I do
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'TREDBA',
tabname => 'MV_DOC_INFO');
END;
My query which use this mv is
SELECT
/*+ ORDERED REWRITE(mv_test) */ doc_info.publication_date,
doc_info.report_title,
doc_info.source_name,
doc_info.source_code,
doc_info.report_num,
doc_info.cd_num,
doc_info.collection,
doc_info.sub_collection,
doc_info.language
-- max(score(0)) relevance
FROM
test_doc doc_page,
test_doc_info doc_info
WHERE
doc_page.report_num = doc_info.report_num AND
doc_info.publication_date >= to_date( '2001-04-04', 'YYYY-MM-DD' ) AND
( contains( doc_page.page_text, 'DEBT', 0 ) > 0 ) AND
(doc_info.collection = 'INV' OR doc_info.collection = 'II' OR doc_info.collection = 'MMN') AND
doc_info.embargo_date <= to_date( '2003-04-29','YYYY-MM-DD')
GROUP BY doc_info.publication_date,
doc_info.report_title,
doc_info.source_name,
doc_info.source_code,
doc_info.report_num,
doc_info.cd_num,
doc_info.collection,
doc_info.sub_collection,
doc_info.language
This query use the mvs and returns the result with 30-40 sec for 45k records
But if i add the
-- max(score(0)) relevance
in the select query it takes about 4 minutes to return the query because it
doesnot use the mv's.
I cannot make the max(Score) part of the mv's as such it is query choice
made by the user
How can I improve this query
I tried using the Dimension type but could not do much as I was getting
some error
Can you highlight how to use dimension if effective
or any other features that could be helpful in achiving the same
Thank you
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 14:19:16 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
