Here are the two table SQL is associated query time to 1 minute

 SELECT * FROM opc_diag_service_h h 
INNER JOIN opc_diag_service_d d ON d.d iag_service_h_id=h.i d 

The execution plan
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
| | Id Operation | Name | Rows | Bytes | Cost | Time |
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
| | 0 SELECT STATEMENT | | 24349752 | 7962368904 | 523937 | 01:44:48 |
| * 1 | HASH JOIN | | 24349752 | 7962368904 | 523937 | 01:44:48 |
| | 2 TABLE ACCESS FULL | OPC_DIAG_SERVICE_H | 10731641 | 1684867637 | 65240 | 00:13:03 |
| 3 | TABLE ACCESS FULL | OPC_DIAG_SERVICE_D | 24349752 | 4139457840 | 163130 | 00:32:38 |
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

From the execution plan on like a token scanning

H table index


D table index

CodePudding user response:

Collect the statistical information and have a look

CodePudding user response:

Statistical information is what, two tables associated the amount of data that a total of 25 million count (*) to 2 minutes, I was in another of the same count (*) statement 47 million data as long as 1 minutes and 20 seconds, the other is a library
TABLE ACCESS STORAGE FULL

CodePudding user response:

reference 1/f, true don't know what to get a reply:

collect statistics see

statistics is what ah

CodePudding user response:

Your statement with the where condition, so there is an index also won't go, because taking a full table scan cost is lower,

CodePudding user response:

You can try through hint force index, query time should be longer

 SELECT/* + index (table IndexName) */

CodePudding user response:

Two connections Zhang Dabiao query, general rule is this:
If the associated column has index, Michael Jackson will go, no index will go HJ, now you this situation,

Moreover, the query without the where condition, almost one hundred percent don't walk index,

The building Lord baidu first: NL, MJ, HJ this a few word,