学习over partition by调优

1、准备数据

DROP TABLE  TEST;

CREATE TABLE TEST(

 ID  VARCHAR2 ( 32 )  PRIMARY KEY  ,

 NAME  VARCHAR2  ( 20 ),

 AGE NUMBER ( 3 )

);

INSERT INTO TEST_Y  VALUES (SYS_GUID(),   '张三' , 21  );

INSERT INTO TEST_Y   VALUES (SYS_GUID(), '张三' , 32  );

INSERT INTO  TEST_Y  VALUES (SYS_GUID(), '贝多芬' , 33  );

INSERT INTO  TEST_Y  VALUES (SYS_GUID(),  '李四' , 35   );

INSERT INTO  TEST_Y  VALUES (SYS_GUID(),   '五五' , 43   );

INSERT INTO  TEST_Y  VALUES (SYS_GUID(),   '小六' , 34  );

INSERT INTO   TEST_Y  VALUES (SYS_GUID(),   '小六' , 21   );

INSERT INTO   TEST_Y   VALUES (SYS_GUID(),   '小六' , 19  );

INSERT INTO  TEST_Y  VALUES (SYS_GUID(),   '甲六' , 28 );

INSERT INTO   TEST_Y  VALUES (SYS_GUID(),   '田四' , 26   );

INSERT INTO   TEST_Y   VALUES (SYS_GUID(),   '春风' , 31   );



SELECT   *   FROM   TEST;

2、根据名字进行查询，如果重名则根据年龄进行排序

select name,age ,ROW_NUMBER() over(partition by name order by age desc)  from TEST;

 

查看执行计划

可以看到上面两个条件走的全表扫描，TEST_Y.NAME和TEST_Y.AGE,首先尝试创建一个组合索引，并且全部是升序，更新统计信息,查看结果

create index "IND_NAME_AGE_TEST" on "SYSDBA"."TEST"("NAME","AGE");

 

再尝试将组合索引的age列改成降序

create or replace  index "IND_NAME_AGE_TEST_Y" on "SYSDBA"."TEST_Y"("NAME","AGE" DESC);

更新统计信息，查看执行计划

发现走了索引，消除了排序