一人一丁云吧(Bar)

基本表CMS_ACCOUNT_SUM(当前记录数1892062条),
索引（唯一）PK_CMS_ACCOUNT_SUM：ACCOUNT_SUM_ID（主键）
索引（正常）CMS_ACCOUNT_SUM_IDX：BRANCH_CODE, FLIGHT_DATE, FLIGHT_NUMBER, AIRCRAFT_REG, AIRCRAFT_TYPE, LEG, COST_ID

基本表CMS_RECEIPT_DETAIL（当前记录数371685条）
索引（唯一）PK_CMS_RECEIPT_DETAIL：DETAIL_ID（主键）
索引（正常）CMS_RECEIPT_DETAIL_IDX：FLIGHT_ID, DETAIL_ID, FEE_ID_D
索引（正常）CMS_RECEIPT_DETAIL_IDX1：FLIGHT_ID, FEE_ID_D, DIFF_STATUS

ORACLE 取得指定行以及随机提取数据ByCinyunCMS

取表中第20行到第30行数据

select *
  from (select rownum as rn, t.*
          from cms_account_info t
          where rownum <= 30)
 where rn >= 20

随机提取50条数据
select *
  from (select rownum, t.*
          from cms_account_info t

UPDATE cms_reverse_lines rev

   SET rev.ADJUST_BATCH_NO = '冲销201111'

 WHERE rev.amount_approved = 0

   AND rev.batch_no is not null

   AND rev.adjust_batch_no is null

   AND substr(rev.cost_id, 1, 9) != '001005004'

   and

exists(select null from cms_reverse_headers a, cms_reverse_lines b

CREATE OR REPLACE PROCEDURE PCMSINTOORACLE(info_num_Str      VARCHAR2,
                                           case_Nor_Tes      IN OUT NUMBER,
                                           p_message         IN OUT VARCHAR2) is

/*
  --Author  : Cinyun Qiu

CREATE OR REPLACE PROCEDURE PCALCULTINTOORA(created_by        VARCHAR2,
                                            last_updated_by   VARCHAR2,
                                            created_time      NUMBER,
            

CREATE OR REPLACE PROCEDURE P_TO_ACCOUNT_SUM_RECEIPTS(p_account_info_num     IN NUMBER,
                                                      p_message          IN OUT VARCHAR2) IS

  --Author  : Cinyun Qiu
  --Created : 2008-04-24
  --Purpose : 审核、预提调整第一步：数据从CMS_ACCOUNT_RECEIPT相关表进入CMS_ACCOUNT_SUM表。
  /*参数说明
  <param name="p_account_info_num">帐务流程ID,一笔帐的唯一标识</param>
  <param name="p_messa

CREATE OR REPLACE PROCEDURE P_TO_ACCOUNT_SUM_RECEIPT(p_account_info_num     IN NUMBER,
                                                     p_message          IN OUT VARCHAR2) IS

  --Author  : Cinyun Qiu
  --Created : 2008-04-24
  --Purpose : 数据从CMS_ACCOUNT_RECEIPT相关表进入CMS_ACCOUNT_SUM表,
  --          本过程需求源自包PREPARED_EXTRACTION_ADJUSTMENT,只是增加了中间表，
  --   

CREATE OR REPLACE PROCEDURE PCMSINTOPROD(created_by        VARCHAR2,
                                         last_updated_by   VARCHAR2,
                                         created_time      NUMBER,
                  

经常别人说EXISTS比IN快！NOT EXISTS比NOT IN快！然而事实真的如此么？

    我们先讨论IN和EXISTS。

    select * from t1 where x in ( select y from t2 )

    事实上可以理解为：

    select *

      from t1, ( select distinct y from t2 ) t2

     where t1.x = t2.y;

    ——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”