GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — cscripts/cs_index_part_reorg.sql at master · oradrs/cscripts

Expand file tree

/

cs_index_part_reorg.sql

Copy path
More file actions
More file actions

Latest commit

 

History

History
History
executable file
·
114 lines (109 loc) · 2.91 KB
/

cs_index_part_reorg.sql

Copy path

File metadata and controls

executable file
·
114 lines (109 loc) · 2.91 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
----------------------------------------------------------------------------------------
--
-- File name: cs_index_part_reorg.sql
--
-- Purpose: Calculate index reorg savings
--
-- Author: Rodrigo Righetti
--
-- Version: 2019/04/16
--
-- Usage: Execute connected to PDB.
--
-- Enter Owner, Table and Index when requested.
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_index_part_reorg.sql
--
-- Notes: Developed and tested on 12.1.0.2.
--
---------------------------------------------------------------------------------------
col partition_name for a20
set pages 67
set lines 150
PRO 1. Table Owner:
DEF table_owner = '&1.';
SELECT DISTINCT UPPER(owner) table_owner
FROM dba_tables
WHERE owner = UPPER(TRIM('&&table_owner.'))
/
PRO 2. Table Name:
DEF table_name = '&2.';
SELECT DISTINCT UPPER(table_name) table_name
FROM dba_tables
WHERE table_name = UPPER(TRIM('&&table_name.'))
/
PRO 3. Index Name:
DEF index_name = '&3.';
SELECT DISTINCT UPPER(index_name) index_name
FROM dba_indexes
WHERE index_name = UPPER(TRIM('&&index_name.'))
/
WITH ca AS (
SELECT /*+ MATERIALIZE */
SUM(avg_col_len) actual_size,
SUM(avg_col_len) * 1.25 est_size
FROM
dba_tab_columns
WHERE
table_name = '&&table_name.'
and owner = '&&table_owner.'
AND column_name IN (
SELECT
column_name
FROM
dba_ind_columns
WHERE
index_name = '&&index_name.'
)
), ps AS (
SELECT /*+ MATERIALIZE */
partition_name,
round(bytes / POWER(2,20), 2) size_mb
FROM
dba_segments
WHERE
segment_name = '&&index_name.'
), pr AS (
SELECT /*+ MATERIALIZE */
partition_name,
num_rows
FROM
dba_ind_partitions
WHERE
index_name = '&&index_name.'
)
SELECT
*
FROM
(
SELECT
pr.partition_name,
pr.num_rows,
ps.size_mb,
round((ca.actual_size * pr.num_rows) / POWER(2,20), 2) est_used_size_mb,
round((ca.est_size * pr.num_rows) / POWER(2,20), 2) est_rebuild_size_mb,
round((1 -(((ca.est_size * pr.num_rows) / POWER(2,20)) / ps.size_mb)) * 100, 2) savings_pct
FROM
pr,
ps,
ca
WHERE
pr.partition_name = ps.partition_name
ORDER BY
round((1 -(((ca.est_size * pr.num_rows) / POWER(2,20)) / ps.size_mb)) * 100, 2)
)
UNION ALL
SELECT
'TOTAL',
SUM(pr.num_rows) num_rows,
SUM(ps.size_mb) size_mb,
SUM(round((ca.actual_size * pr.num_rows) / POWER(2,20), 2)) est_used_size_mb,
SUM(round((ca.est_size * pr.num_rows) / POWER(2,20), 2)) est_rebuild_size_mb,
round((1 -(SUM(round((ca.est_size * pr.num_rows) / POWER(2,20), 2)) / SUM(ps.size_mb))) * 100, 2) savings_pct
FROM
pr,
ps,
ca
WHERE
pr.partition_name = ps.partition_name;
You are visitor 01402520 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.