T1 table: rids, VID, RID: a report ID, VID is the patient ID, water a VID corresponding multiple rids,

R1 table: it is a remote Oracle table, VID primary key, according to the patient basic water information,

V view: select T.R ID, T.V ID, r. * from T1 left join R1 on T1. The VID=R1. VID.

Our query is: select * from V where rids=XXX;

This search is very slow,

If executed: select * from V where VID=XXX is very fast,

This have what method, can let the Oracle, consult the V in the view on the left side of the T1, results, and associated remote table R1. So soon,

Have similar to this Hint, table tips, you can specify, patients

CodePudding user response:

That is left, to participate in the connection, must be the T1 table as the driver table, that is what you said "consult the T1 table,"

Confirm the T1 table rids of whether there is an index on field? If so, give a slow query execution plan, the trouble, can first last PLSQL developer F5 execution plan

CodePudding user response:

The T1 table as the main table, can't add hint hint you say again,
If stubbornly add hint, only check T1. Has a VID index, finally add hint hint T1 table index,

CodePudding user response:

Such that the T1 table, rids is a primary key, VID is indexed,
R1 table, VID index,

I query V, the select * from V where rids

CodePudding user response:

refer to the second floor qq646748739 response:

T1 table as the main table, can't add hint hint you say again,
If stubbornly add hint, only check T1. Has a VID index, finally add hint hint T1 table index,

How to add?

CodePudding user response:

If have indexes on rids, then confirm one thing: RID varchar2 type, and conditions in writing is this: the where rids=123456, rather than the where rids='123456'
If this is not the case, please ~ execution plans for further diagnosis

CodePudding user response:

reference 5 floor minsic78 reply:

if rids has built up the index, then confirm one thing: whether rids varchar2 type, and conditions in writing is this: the where rids=123456, rather than the where rids='123456'
If this is not the case, please plan for further diagnosis ~

Rids are strings, the query is also in accordance with the where rids='XXX', but I found a T1. VID Index is more columns, Index (PID, VID), this Index may use less than,

CodePudding user response:

refer to 6th floor wcl1986_1 response:

Quote: refer to the fifth floor minsic78 reply:

If have indexes on rids, then confirm one thing: RID varchar2 type, and conditions in writing is this: the where rids=123456, rather than the where rids='123456'
If this is not the case, please plan for further diagnosis ~

Rids are strings, the query is also in accordance with the where rids='XXX', but I found a T1. VID Index is more columns, Index (PID, VID), this Index may use less than,

What you said is not VID query above no problem? There is RID of the problem

CodePudding user response:

refer to 7th floor minsic78 response:

Quote: refer to the sixth floor wcl1986_1 response:

Quote: refer to the fifth floor minsic78 reply:

If have indexes on rids, then confirm one thing: RID varchar2 type, and conditions in writing is this: the where rids=123456, rather than the where rids='123456'
If this is not the case, please plan for further diagnosis ~

Rids are strings, the query is also in accordance with the where rids='XXX', but I found a T1. VID Index is more columns, Index (PID, VID), this Index may use less than,

What you said is not VID query above no problem? Problem is rids

If you actually said that indexes were (PID, rids), then consider whether can adjust the combination of field order of the index, after the transfer, of course, if you have SQL used by PID, and on the table without PID as the index of leading field, then PID query will be a problem, or, simply create an index for rids also,

CodePudding user response:

T1 table VID is the Index (PID, VID) is right,
I try to establish a VID index.

CodePudding user response:

references 9 f wcl1986_1 response:

T1 table VID, is the Index (PID, VID) is right,
I try to establish a VID index,

But your VID query is not ok? Also built index of VID?

CodePudding user response:

Query VID is soon,
But when query rids, use the Join, is to use VID link, I would like to link key, if no index, may affect the optimization tactics of the Oracle,
I guess, if the T1 and R1 VID has index, can query the where rids='XXX' will be faster,

CodePudding user response:

11 references wcl1986_1 response:

query VID is soon,
But when query rids, use the Join, is to use VID link, I would like to link key, if no index, may affect the optimization tactics of the Oracle,
I guess, if the T1 and R1 VID has index, can query the where rids='XXX' will be faster,

So I asked from the beginning there is implementation plan, or elephant to guess all the time,

From the CBO general behavior, at the time of the cross-database queries, he will do his best to let the overhead on the network become less, so, not too big surprise, rids the predicate is bound to be being pushed into the internal view to filter the T1 table, after the filter data to the remote database do join, look at your application, normally you should is a nested loop, will be the driver table, go, that is, the remote R1 indexes on the table join field, in this process, the T1 table index of VID field is useless, unless you create a cover index, rids, VID is built on a index,

In addition, in fact I always doubt you what T1 and R1 is for security reasons, is processed, and even some writing SQL you may also be done, I am afraid that after the modification of SQL, the problem of the judgement of misleading, there is a big

CodePudding user response:

And more verbose, dblink this kind of thing, can not, do not have to use, there is no way from the performance or some other considerations, unless you R1 table and table T1 connection, high real-time demand of data, or Suggestions would be R1 table brush use materialized views to the local library, at least to do join, although your application now seems not complex, but if there are involved in what applications, such as data statistics might ha ha ~

CodePudding user response:

Give an execution plan, the guess is not good,

CodePudding user response:

It is hard to say, you said quickly, is likely to be detected the T1 data, just in front of more than 10, but not all, and didn't read the other table, try to build a temporary table, the access to check in and see, compare two speed!

CodePudding user response:

refer to 6th floor wcl1986_1 response:

Quote: refer to the fifth floor minsic78 reply:

If have indexes on rids, then confirm one thing: RID varchar2 type, and conditions in writing is this: the where rids=123456, rather than the where rids='123456'
If this is not the case, please plan for further diagnosis ~

Rids are strings, the query is also in accordance with the where rids='XXX', but I found a T1. VID Index is more columns, Index (PID, VID), this Index may use less than,

Index (PID, VID) is the Index of PID, or PID, VID combination conditions, separate VID is not effective, composite Index is effective to lead, but a new version of the oracle on the do not know to have to do, if is as an Index to build two separate proposals Indexnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull