类似groupby的分组计数功能
之前同事发过一个语句，实现的功能比较简单，类似group by的分组计数功能，因为where条件有like，又无法用group by来实现。SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from (select count(*) N0 from tbl_loginfo_20141110 where keyrecord
之前同事发过一个语句，实现的功能比较简单，类似group by的分组计数功能，因为where条件有like，又无法用group by来实现。
SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from
(select count(*) N0 from tbl_loginfo_20141110 where keyrecord like '0%' or keyrecord like 'GJ_0%') a,
(select count(*) N1 from tbl_loginfo_20141110 where keyrecord like '1%' or keyrecord like 'GJ_1%') b,
(select count(*) N2 from tbl_loginfo_20141110 where keyrecord like '2%' or keyrecord like 'GJ_2%') c,
(select count(*) N3 from tbl_loginfo_20141110 where keyrecord like '3%' or keyrecord like 'GJ_3%') d,
(select count(*) N4 from tbl_loginfo_20141110 where keyrecord like '4%' or keyrecord like 'GJ_4%') e,
(select count(*) N5 from tbl_loginfo_20141110 where keyrecord like '5%' or keyrecord like 'GJ_5%') f,
(select count(*) N6 from tbl_loginfo_20141110 where keyrecord like '6%' or keyrecord like 'GJ_6%') g,
(select count(*) N7 from tbl_loginfo_20141110 where keyrecord like '7%' or keyrecord like 'GJ_7%') h,
(select count(*) N8 from tbl_loginfo_20141110 where keyrecord like '8%' or keyrecord like 'GJ_8%') i,
(select count(*) N9 from tbl_loginfo_20141110 where keyrecord like '9%' or keyrecord like 'GJ_9%') j;
为了了解语句的性能，我做了如下类似的测试：
select * from v$version; --Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
drop table a;
create table a as select * from dba_objects where rownum<=50000;
begin
for x in 1..6 loop
insert into a select * from a;
end loop;
commit;
end;
select count(*) from a; --3200000
select bytes/1024/1024 from user_segments where segment_name='A'; --357M
alter system flush shared_pool;
alter system flush buffer_cache;
SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from
(select count(*) N0 from a where object_name like 'A%' or object_name like 'V%') a,
(select count(*) N1 from a where object_name like 'B%' or object_name like 'V%') b,
(select count(*) N2 from a where object_name like 'C%' or object_name like 'V%') c,
(select count(*) N3 from a where object_name like 'D%' or object_name like 'V%') d,
(select count(*) N4 from a where object_name like 'E%' or object_name like 'V%') e,
(select count(*) N5 from a where object_name like 'F%' or object_name like 'V%') f,
(select count(*) N6 from a where object_name like 'G%' or object_name like 'V%') g,
(select count(*) N7 from a where object_name like 'H%' or object_name like 'V%') h,
(select count(*) N8 from a where object_name like 'I%' or object_name like 'V%') i,
(select count(*) N9 from a where object_name like 'J%' or object_name like 'V%') j;
--58s
alter system flush shared_pool;
alter system flush buffer_cache;
--改写后
select
sum(case when object_name like 'A%' or object_name like 'V%' then 1 else 0 end) N0,
sum(case when object_name like 'B%' or object_name like 'V%' then 1 else 0 end) N1,
sum(case when object_name like 'C%' or object_name like 'V%' then 1 else 0 end) N2,
sum(case when object_name like 'D%' or object_name like 'V%' then 1 else 0 end) N3,
sum(case when object_name like 'E%' or object_name like 'V%' then 1 else 0 end) N4,
sum(case when object_name like 'F%' or object_name like 'V%' then 1 else 0 end) N5,
sum(case when object_name like 'G%' or object_name like 'V%' then 1 else 0 end) N6,
sum(case when object_name like 'H%' or object_name like 'V%' then 1 else 0 end) N7,
sum(case when object_name like 'I%' or object_name like 'V%' then 1 else 0 end) N8,
sum(case when object_name like 'J%' or object_name like 'V%' then 1 else 0 end) N9
from a;
--19s
--对比执行计划：
--前者执行计划：
SQL> explain plan for
2 SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from
3 (select count(*) N0 from a where object_name like 'A%' or object_name like 'V%') a,
4 (select count(*) N1 from a where object_name like 'B%' or object_name like 'V%') b,
5 (select count(*) N2 from a where object_name like 'C%' or object_name like 'V%') c,
6 (select count(*) N3 from a where object_name like 'D%' or object_name like 'V%') d,
7 (select count(*) N4 from a where object_name like 'E%' or object_name like 'V%') e,
8 (select count(*) N5 from a where object_name like 'F%' or object_name like 'V%') f,
9 (select count(*) N6 from a where object_name like 'G%' or object_name like 'V%') g,
10 (select count(*) N7 from a where object_name like 'H%' or object_name like 'V%') h,
11 (select count(*) N8 from a where object_name like 'I%' or object_name like 'V%') i,
12 (select count(*) N9 from a where object_name like 'J%' or object_name like 'V%') j;
Explained.
Elapsed: 00:00:00.15
SQL> @getplan
'general,outline,starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2527411742
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 123K (1)| 00:24:46 |
| 1 | NESTED LOOPS | | 1 | 130 | 123K (1)| 00:24:46 |
| 2 | NESTED LOOPS | | 1 | 117 | 111K (1)| 00:22:17 |
| 3 | NESTED LOOPS | | 1 | 104 | 99032 (1)| 00:19:49 |
| 4 | NESTED LOOPS | | 1 | 91 | 86653 (1)| 00:17:20 |
| 5 | NESTED LOOPS | | 1 | 78 | 74274 (1)| 00:14:52 |
| 6 | NESTED LOOPS | | 1 | 65 | 61895 (1)| 00:12:23 |
| 7 | NESTED LOOPS | | 1 | 52 | 49516 (1)| 00:09:55 |
| 8 | NESTED LOOPS | | 1 | 39 | 37137 (1)| 00:07:26 |
| 9 | NESTED LOOPS | | 1 | 26 | 24758 (1)| 00:04:58 |
| 10 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 11 | SORT AGGREGATE | | 1 | 66 | | |
|* 12 | TABLE ACCESS FULL| A | 91587 | 5903K| 12379 (1)| 00:02:29 |
| 13 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 14 | SORT AGGREGATE | | 1 | 66 | | |
|* 15 | TABLE ACCESS FULL| A | 137K| 8831K| 12379 (1)| 00:02:29 |
| 16 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 17 | SORT AGGREGATE | | 1 | 66 | | |
|* 18 | TABLE ACCESS FULL | A | 85818 | 5531K| 12379 (1)| 00:02:29 |
| 19 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 20 | SORT AGGREGATE | | 1 | 66 | | |
|* 21 | TABLE ACCESS FULL | A | 111K| 7158K| 12379 (1)| 00:02:29 |
| 22 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 23 | SORT AGGREGATE | | 1 | 66 | | |
|* 24 | TABLE ACCESS FULL | A | 86539 | 5577K| 12379 (1)| 00:02:29 |
| 25 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 26 | SORT AGGREGATE | | 1 | 66 | | |
|* 27 | TABLE ACCESS FULL | A | 91587 | 5903K| 12379 (1)| 00:02:29 |
| 28 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 29 | SORT AGGREGATE | | 1 | 66 | | |
|* 30 | TABLE ACCESS FULL | A | 228K| 14M| 12379 (1)| 00:02:29 |
| 31 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 32 | SORT AGGREGATE | | 1 | 66 | | |
|* 33 | TABLE ACCESS FULL | A | 87981 | 5670K| 12379 (1)| 00:02:29 |
| 34 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 35 | SORT AGGREGATE | | 1 | 66 | | |
|* 36 | TABLE ACCESS FULL | A | 84376 | 5438K| 12379 (1)| 00:02:29 |
| 37 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 |
| 38 | SORT AGGREGATE | | 1 | 66 | | |
|* 39 | TABLE ACCESS FULL | A | 112K| 7251K| 12379 (1)| 00:02:29 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - filter("OBJECT_NAME" LIKE 'J%' OR "OBJECT_NAME" LIKE 'V%')
15 - filter("OBJECT_NAME" LIKE 'I%' OR "OBJECT_NAME" LIKE 'V%')
18 - filter("OBJECT_NAME" LIKE 'H%' OR "OBJECT_NAME" LIKE 'V%')
21 - filter("OBJECT_NAME" LIKE 'G%' OR "OBJECT_NAME" LIKE 'V%')
24 - filter("OBJECT_NAME" LIKE 'F%' OR "OBJECT_NAME" LIKE 'V%')
27 - filter("OBJECT_NAME" LIKE 'E%' OR "OBJECT_NAME" LIKE 'V%')
30 - filter("OBJECT_NAME" LIKE 'D%' OR "OBJECT_NAME" LIKE 'V%')
33 - filter("OBJECT_NAME" LIKE 'C%' OR "OBJECT_NAME" LIKE 'V%')
36 - filter("OBJECT_NAME" LIKE 'B%' OR "OBJECT_NAME" LIKE 'V%')
39 - filter("OBJECT_NAME" LIKE 'A%' OR "OBJECT_NAME" LIKE 'V%')
--后者执行计划：
SQL> explain plan for
2 select
3 sum(case when object_name like 'A%' or object_name like 'V%' then 1 else 0 end) N0,
4 sum(case when object_name like 'B%' or object_name like 'V%' then 1 else 0 end) N1,
5 sum(case when object_name like 'C%' or object_name like 'V%' then 1 else 0 end) N2,
6 sum(case when object_name like 'D%' or object_name like 'V%' then 1 else 0 end) N3,
7 sum(case when object_name like 'E%' or object_name like 'V%' then 1 else 0 end) N4,
8 sum(case when object_name like 'F%' or object_name like 'V%' then 1 else 0 end) N5,
9 sum(case when object_name like 'G%' or object_name like 'V%' then 1 else 0 end) N6,
10 sum(case when object_name like 'H%' or object_name like 'V%' then 1 else 0 end) N7,
11 sum(case when object_name like 'I%' or object_name like 'V%' then 1 else 0 end) N8,
12 sum(case when object_name like 'J%' or object_name like 'V%' then 1 else 0 end) N9
13 from a;
Explained.
Elapsed: 00:00:00.01
SQL> @getplan
'general,outline,starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3918351354
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 12349 (1)| 00:02:29 |
| 1 | SORT AGGREGATE | | 1 | 66 | | |
| 2 | TABLE ACCESS FULL| A | 3097K| 194M| 12349 (1)| 00:02:29 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
可以看出，前者10次全表扫描，后者1次全表扫描。从而时间上也大大降低了。由58s降低到19s。
优化这个sql主要还是思路的转换，难点在于怎样把10次全表扫描转化成1次全表扫描。
在OLAP中，可以加并行使sql速度更快。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
What functions does Doubao app have?
Mar 01, 2024 pm 10:04 PM
There will be many AI creation functions in the Doubao app, so what functions does the Doubao app have? Users can use this software to create paintings, chat with AI, generate articles for users, help everyone search for songs, etc. This function introduction of the Doubao app can tell you the specific operation method. The specific content is below, so take a look! What functions does the Doubao app have? Answer: You can draw, chat, write articles, and find songs. Function introduction: 1. Question query: You can use AI to find answers to questions faster, and you can ask any kind of questions. 2. Picture generation: AI can be used to create different pictures for everyone. You only need to tell everyone the general requirements. 3. AI chat: can create an AI that can chat for users,
The difference between vivox100s and x100: performance comparison and function analysis
Mar 23, 2024 pm 10:27 PM
Both vivox100s and x100 mobile phones are representative models in vivo's mobile phone product line. They respectively represent vivo's high-end technology level in different time periods. Therefore, the two mobile phones have certain differences in design, performance and functions. This article will conduct a detailed comparison between these two mobile phones in terms of performance comparison and function analysis to help consumers better choose the mobile phone that suits them. First, let’s look at the performance comparison between vivox100s and x100. vivox100s is equipped with the latest
Comparative analysis of the functions and performance of JPA and MyBatis
Feb 19, 2024 pm 05:43 PM
JPA and MyBatis: Function and Performance Comparative Analysis Introduction: In Java development, the persistence framework plays a very important role. Common persistence frameworks include JPA (JavaPersistenceAPI) and MyBatis. This article will conduct a comparative analysis of the functions and performance of the two frameworks and provide specific code examples. 1. Function comparison: JPA: JPA is part of JavaEE and provides an object-oriented data persistence solution. It is passed annotation or X
What exactly is self-media? What are its main features and functions?
Mar 21, 2024 pm 08:21 PM
With the rapid development of the Internet, the concept of self-media has become deeply rooted in people's hearts. So, what exactly is self-media? What are its main features and functions? Next, we will explore these issues one by one. 1. What exactly is self-media? We-media, as the name suggests, means you are the media. It refers to an information carrier through which individuals or teams can independently create, edit, publish and disseminate content through the Internet platform. Different from traditional media, such as newspapers, television, radio, etc., self-media is more interactive and personalized, allowing everyone to become a producer and disseminator of information. 2. What are the main features and functions of self-media? 1. Low threshold: The rise of self-media has lowered the threshold for entering the media industry. Cumbersome equipment and professional teams are no longer needed.
What is Discuz? Definition and function introduction of Discuz
Mar 03, 2024 am 10:33 AM
"Exploring Discuz: Definition, Functions and Code Examples" With the rapid development of the Internet, community forums have become an important platform for people to obtain information and exchange opinions. Among the many community forum systems, Discuz, as a well-known open source forum software in China, is favored by the majority of website developers and administrators. So, what is Discuz? What functions does it have, and how can it help our website? This article will introduce Discuz in detail and attach specific code examples to help readers learn more about it.
What is a Bluetooth adapter used for?
Feb 19, 2024 pm 05:22 PM
What does a Bluetooth adapter do? With the continuous development of science and technology, wireless communication technology has also been rapidly developed and popularized. Among them, Bluetooth technology, as a short-distance wireless communication technology, is widely used in data transmission and connection between various devices. The Bluetooth adapter plays a vital role as an important device that supports Bluetooth communication. A Bluetooth adapter is a device that can turn a non-Bluetooth device into a device that supports Bluetooth communication. It realizes wireless connection and data transmission between devices by converting wireless signals into Bluetooth signals. Bluetooth adapter
PHP Tips: Quickly Implement Return to Previous Page Function
Mar 09, 2024 am 08:21 AM
PHP Tips: Quickly implement the function of returning to the previous page. In web development, we often encounter the need to implement the function of returning to the previous page. Such operations can improve the user experience and make it easier for users to navigate between web pages. In PHP, we can achieve this function through some simple code. This article will introduce how to quickly implement the function of returning to the previous page and provide specific PHP code examples. In PHP, we can use $_SERVER['HTTP_REFERER'] to get the URL of the previous page
What are the functions of Xiaohongshu account management software? How to operate a Xiaohongshu account?
Mar 21, 2024 pm 04:16 PM
As Xiaohongshu becomes popular among young people, more and more people are beginning to use this platform to share various aspects of their experiences and life insights. How to effectively manage multiple Xiaohongshu accounts has become a key issue. In this article, we will discuss some of the features of Xiaohongshu account management software and explore how to better manage your Xiaohongshu account. As social media grows, many people find themselves needing to manage multiple social accounts. This is also a challenge for Xiaohongshu users. Some Xiaohongshu account management software can help users manage multiple accounts more easily, including automatic content publishing, scheduled publishing, data analysis and other functions. Through these tools, users can manage their accounts more efficiently and increase their account exposure and attention. In addition, Xiaohongshu account management software has
