Oracle :show explain plan

select * from table(dbms_xplan.display);

EXPLAIN PLAN FOR statements

In fact, in a typical data warehouse environments, a bitmap index can be considered for any non-unique column.

In a data warehouse, B-tree indexes should be used only for unique columns or other columns with very high cardinalities (that is, columns that are almost unique). The majority of indexes in a data warehouse should be bitmap indexes.

One stage table, is a heap table, need always insert and delete, and we found it waste a lot of blocks.

How to fix this issue?

Use online –redefine.

In sqlserver, we can just create a cluster index on this table, then drop this index, it will be ok.

The following plsql is used to check the table's block used status:

Check table's used space percent:

SELECT TABLE_NAME,HWM,AVG_USED_BLOCKS,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt
from
(SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),
0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)
) + 2 AVG_USED_BLOCKS
FROM USER_SEGMENTS A,
USER_TABLES B
WHERE SEGMENT_NAME = TABLE_NAME
and TABLE_NAME in ( SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'XXX_%')
and SEGMENT_TYPE = 'TABLE'
)ORDER BY WASTE_PER DESC;

Oracle PLSQL More articles about

  1. Oracle/PLSQL: ORA-06550

    Reference resources : http://blog.csdn.net/haiross/article/details/20612135 Oracle/PLSQL: ORA-06550 Learn the cause an ...

  2. Oracle PLSQL Read ( analysis )Excel file

    http://www.itpub.net/thread-1921612-1-1.html !!!https://code.google.com/p/plsql-utils/ Introduction Medium ...

  3. MyEclipse+Weblogic+Oracle+PLSQL Configuration considerations

    Weblogic Configuration details :<Weblogic Installation and configuration details >Oracle+PLSQL Configuration details :<PL/SQL Access remote Oracle The server ( A variety of ways )>MyEclipse To configure : ...

  4. oracle Plsql function update perhaps delete The way to solve the problem of time jam

    oracle Plsql function update perhaps delete when Met Plsql Stuck or causing code to run sql It's stuck when you're in the car . When you encounter this problem in development , Originally sql Copy it , stay plsql Run in ,Sql Ben ...

  5. oracle plsql Basic grammar

    oracle plsql Basic grammar --plsql The default rules :plsql For assignment ":=" plsql Judge with "=" plsql For input "&" ...

  6. Oracle/PLSQL Stored procedure details

    Link to the original text :https://blog.csdn.net/zezezuiaiya/article/details/79557621 Oracle/PLSQL Stored procedure details 2018-03-14 17:31: ...

  7. Not installed locally Oracle,plsql Remote connection database

    because Oracle The size of , Sometimes we just need to install Oracle Client such as plsql.toad And so on to connect to the remote database , But it's not installed Oracle You don't have all the configuration files to support . Finally found a very effective method ,Or ...

  8. Oracle PLSQL Statement instance

    /** * plsql: When a project requires high efficiency , Generally do not use , Most of the projects are written on the database platform , It will not be on . * pssql Most of the things you can do ,java Can replace it . It is required to use in some special places plsql When ...

  9. Oracle / PLSQL Functions often used when writing statements

    I've been learning about databases recently , Make a mark . Here's an English explanation , It is suggested to read more English documents : https://www.techonthenet.com/oracle/functions/ Let's start recording , in Or ...

Random recommendation

  1. Entity Framework The formation of Entity Framework -- Unified base class interface and implementation of asynchronous operation (3)

    In the first essay of this series <Entity Framework The formation of Entity Framework -- Entity Framework of warehouse pattern based on generics (1)> This paper introduces the in Entity Framework Some basic knowledge of Entity Framework , And build ...

  2. Ubuntu 14.04 Install the latest stable version Nginx 1.6.0

    If installed , Please uninstall first sudo apt-get remove nginx The latest stable version Nginx 1.6.0 stay ubuntuupdates ppa The library provides , website http://www.ubuntuupdat ...

  3. Struts2: Tag library

    Use struts The label of the library jsp You need to introduce a tag library <%@taglib prefix="s" uri="/struts-tags"%>url Where are from ? st ...

  4. Git Skills summary and sharing

    Contact Git For a long time , From the initial do not understand to gradually familiar with the use of , Compared with SVN, More interested in Git This is a powerful version control tool . I don't say much nonsense , Following pair Git Made a summary of some techniques , Share here , Hope to help some people like Git Friends ...

  5. Codeforces Round #281 (Div. 2) C. Vasya and Basketball Two points

    C. Vasya and Basketball time limit per test 2 seconds memory limit per test 256 megabytes input stan ...

  6. 【BZOJ1005】【HNOI2008】 The obvious trouble

    It's written by Huang Xuechang's code again , Estimate my whole BZOJ Horizontal push plan depends on Huang Xuechang's code writing The original title is : Since Mingming learned the structure of trees , I became interested in strange trees ...... The label given is 1 To N The point of , And the final degree of some points , Allow connection between any two points ...

  7. Structural mode (Structural patterns)-&gt; Appearance mode (Facade Pattern)

    motivation (Motivate): In a software development system , Client programs are often coupled with the internal subsystems of complex systems , And the client program changes with the subsystem . So how to simplify the interaction interface between client program and subsystem ? How to integrate the internal subsystems of a complex system ...

  8. .net Kafka.Client Multiple Consumer Group Yes Topic Consumption cannot completely cover the research summary ( One )

    We know Kafka Support Consumer Group The function of , But it's being applied recently Consumer Group When I found a Topic Of Partition You can't 100% The problem of coverage . After the program is deployed , Find out Kafka stay p ...

  9. 【 Algorithm exercises 】 The sum of a positive integer array is sum Any number of combinations of

    1. Recursive implementation ( Reference resources :https://blog.csdn.net/hit_lk/article/details/53967627) public class Test { @org.junit.Test ...

  10. How to... Gracefully Spring Realize the sending and consumption of messages in

    This article will rocktmq-spring-boot Design and implementation of a simple introduction , Readers can learn about RocketMQ Client End to end integration spring-boot-starter The development details of the framework , And then through a ...