关联条件为主外键字段，查询列只有子表
CREATE TABLE parent (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL,
CONSTRAINT parent_pk PRIMARY KEY (id)
);
INSERT INTO parent VALUES (1, 'PARENT ONE');
INSERT INTO parent VALUES (2, 'PARENT TWO');
COMMIT;
CREATE TABLE child (
id NUMBER NOT NULL,
parent_id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL,
CONSTRAINT child_pk PRIMARY KEY (id),
CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id)
);
CREATE INDEX child_parent_fk_idx ON child(parent_id);
INSERT INTO child VALUES (1, 1, 'CHILD ONE');
INSERT INTO child VALUES (2, 1, 'CHILD ONE');
INSERT INTO child VALUES (3, 2, 'CHILD TWO');
INSERT INTO child VALUES (4, 2, 'CHILD TWO');
COMMIT;
analyze parent;
analyze child;
explain analyze
SELECT c.id, c.parent_id, c.description
FROM child c
JOIN parent p ON c.parent_id = p.id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.04..2.14 rows=4 width=20) (actual time=0.112..0.116 rows=4 loops=1)
Hash Cond: (c.parent_id = p.id)
-> Seq Scan on child c (cost=0.00..1.04 rows=4 width=20) (actual time=0.011..0.011 rows=4 loops=1)
-> Hash (cost=1.02..1.02 rows=2 width=5) (actual time=0.018..0.018 rows=2 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1kB
-> Seq Scan on parent p (cost=0.00..1.02 rows=2 width=5) (actual time=0.006..0.008 rows=2 loops=1)
Total runtime: 0.233 ms
(7 rows)
Time: 2.374 ms
13:10:42 aics@cfplm=>
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT c.id, c.parent_id, c.description
FROM child c
JOIN parent p ON c.parent_id = p.id;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 64 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CHILD | 4 | 64 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------