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
Previous Topic: i want replicate data from oracle 8.1.5 database in linux to oracle 8.1.7 in windows
Next Topic: Replication/Backup- urgent pls
Goto Forum:
  


Current Time: Thu Sep 23 08:18:33 CDT 2021